﻿Imports System.Data
Imports System.Data.SqlClient
Public Class ModelosDAL
    Public Sub New()
        'contrutor padrão
    End Sub
    Dim cmd As SqlCommand
    Dim objD As Dados
#Region "Métodos"
    Public Sub inserir(mar_codigo As Integer, mod_nome As String)
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "insert into modelos(mar_codigo,mod_nome) values (@mar_codigo,@mod_nome)"
            cmd.Parameters.Add(New SqlParameter("@mar_codigo", SqlDbType.Int)).Value = mar_codigo
            cmd.Parameters.Add(New SqlParameter("@mod_nome", SqlDbType.VarChar)).Value = mod_nome
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub
    Public Sub alterar(mar_codigo As Integer, mod_nome As String, mod_codigo As Integer)
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "update modelos set mar_codigo = @mar_codigo,mod_nome = @mod_nome " & _
                "where mod_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@mar_codigo", SqlDbType.Int)).Value = mar_codigo
            cmd.Parameters.Add(New SqlParameter("@mod_nome", SqlDbType.VarChar)).Value = mod_nome
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = mod_codigo
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub
    Public Sub excluir(mod_codigo As Integer)
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "delete from modelos where mod_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = mod_codigo
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub
    Public Function localizar(ByVal modelo As String, ByVal mar_codigo As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "select mo.mod_codigo, mo.mod_nome, ma.mar_nome from modelos mo, marcas ma where mo.mar_codigo = @mar_cod and mo.mar_codigo = ma.mar_codigo and mo.mod_nome like @mod_nome "
            cmd.Parameters.Add(New SqlParameter("@mar_cod", SqlDbType.Int)).Value = mar_codigo
            cmd.Parameters.Add(New SqlParameter("@mod_nome", SqlDbType.VarChar)).Value = modelo + "%"
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Public Function localizar(ByVal codmodelo As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "select * from modelos where mod_codigo = @codigo"

            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codmodelo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizar(ByVal descricao As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (descricao = "ultimo") Then
                cmd.CommandText = "SELECT mod_codigo = max(mod_codigo) FROM modelos"
            ElseIf (descricao = "primeiro") Then
                cmd.CommandText = "SELECT mod_codigo = min(mod_codigo) FROM modelos"
            Else
                'Comando para localizar vários registros no banco
                cmd.CommandText = "select mo.mod_codigo,ma.mar_nome,mo.mod_nome " +
                                    "from modelos mo " +
                                    "inner join marcas ma on ma.mar_codigo = mo.mar_codigo " +
                                    "where mo.mod_nome like @descricao"
                'Parâmetro de entrada
                cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & descricao & "%"
            End If
            'Execução do comando
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        'tab = Nothing
    End Function

    Public Function localizaMarca(ByVal codigoModelo As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "select mar_codigo from modelos where mod_codigo  = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigoModelo
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Public Function localizaMarcaEx(ByVal codigoMarca As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "select mar_codigo from modelos where mar_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigoMarca
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizarProxAnterior(ByVal descricao As String, ByVal codigo As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (descricao = "proximo") Then
                cmd.CommandText = "SELECT mod_codigo FROM modelos WHERE mod_codigo = (SELECT MIN(mod_codigo) FROM modelos WHERE mod_codigo > @codigo)"

            ElseIf (descricao = "anterior") Then
                cmd.CommandText = "SELECT mod_codigo FROM modelos  WHERE mod_codigo = (SELECT MAX(mod_codigo) FROM modelos WHERE mod_codigo < @codigo)"
            End If
            'Parâmetro de entrada
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            'Execução do comando
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        'tab = Nothing
    End Function
#End Region
End Class
